/*******************************************************************************
TablesI_II_V.do

This file creates tables to show sorting results using data created in 
"Code\Data_setup\NETS_Sorting_Data.do". It regresses firm's log average 
employment density on its log total employment and industry fixed effects. 
Unless specified, results are produced using a firm's average employment density 
across locations weighted by number of plants for M=12.

Table I: baseline M=12, baseline M=48, firms with at least 100 plants, industries
		 in which the largest firm has at least 100 plants, firms with at least 
		 100 plants while controlling for HQ location fixed effects

Table II: all industries, manufacturing, services, retail trade, FIRE
		 
Table V:
1) baseline M=3, 6, 12, 24, 48
2) firms with at least X=10, 20, 50, 100 plants (M=12)
3) firms with at least X=2, 10, 20, 50, 100 plants, controlling for HQ location 
   fixed effects (M=12)
4) industries where the largest firm has at least X=10, 20, 50, 100 plants (M=12)
5) industries where the largest firm has at least X=10, 20, 50, 100 plants while
   controlling for HQ location fixed effects (M=12)
6) additional results: excluding own firm contribution to local employment
   density, alternative weighting schemes, using non-imputed data (M=12)

Last updated: 5/5/2021
*******************************************************************************/

version 15
cd "C:\Plants_in_Space"
set more off
set type double
set varabbrev off
graph set window fontface "Times New Roman" 

******************Table I: Sorting Firm Size and Local Density******************
// Baseline M=12
use "Data\Final\TablesI_II_V_and_Figure13\firm_average_location_density_M12_data.dta", clear
label variable log_firm_employment "$\ln L_{j}$"
reghdfe log_estab_wght_emp_density_12 log_firm_employment, a(sic8) vce(robust)
outreg2 using "Tables\TableI.tex", label replace tex(fragment) nocons ///
	ctitle("\textit{Baseline}", "\textit{$ M = 12$}", "$\hspace{3.2em}\ln \bar{L}_{j}\hspace{3.2em}$") addtext(SIC8 FE, Yes, HQ Location FE, No, M, 12)

preserve
	// Baseline M=48
	use "Data\Final\TablesI_II_V_and_Figure13\firm_average_location_density_M48_data.dta", clear
	label variable log_firm_employment "$\ln L_{j}$"
	reghdfe log_estab_wght_emp_density_48 log_firm_employment, a(sic8) vce(robust)
	outreg2 using "Tables\TableI.tex", label append tex(fragment) nocons ///
		ctitle("\textit{Baseline}", "\textit{$ M = 48$}", "$\hspace{3.2em}\ln \bar{L}_{j}\hspace{3.2em}$") addtext(SIC8 FE, Yes, HQ Location FE, No, M, 48)
restore

preserve
	// Firms with at least 100 plants
	merge m:1 hq sic8 using "Data\Intermediate\NETS\NETS_HQs_at_least_5_employees_2014_hq-sic8_of_firms_with_at_least_100_plants.dta", assert(1 3) keep(3) nogen
	reghdfe log_estab_wght_emp_density_12 log_firm_employment, a(sic8) vce(robust)
	outreg2 using "Tables\TableI.tex", label append tex(fragment) nocons ///
		ctitle("\textit{Firms with}", "\textit{$\geq 100$ plants}", "$\hspace{3.2em}\ln \bar{L}_{j}\hspace{3.2em}$") addtext(SIC8 FE, Yes, HQ Location FE, No, M, 12)
restore

preserve
	// Industries where largest firm has at least 100 plants
	merge m:1 hq sic8 using "Data\Intermediate\NETS\NETS_HQs_at_least_5_employees_2014_hq-sic8_of_firms_in_industries_where_largest_firm_has_at_least_100_plants.dta", keep(3) nogen
	reghdfe log_estab_wght_emp_density_12 log_firm_employment, a(sic8) vce(robust)
	outreg2 using "Tables\TableI.tex", label append tex(fragment) nocons ///
		ctitle("\textit{Industries in which}", "\textit{largest firms has}", "\textit{$\geq 100$ plants}", "$\hspace{3.2em}\ln \bar{L}_{j}\hspace{3.2em}$") addtext(SIC8 FE, Yes, HQ Location FE, No, M, 12)
restore

preserve
	// Firms with at least 100 plants while controlling for HQ location
	merge m:1 hq sic8 using "Data\Intermediate\NETS\NETS_HQs_at_least_5_employees_2014_hq-sic8_of_firms_with_at_least_100_plants.dta", assert(1 3) keep(3) nogen
	rename hq establishment_hq
	merge m:1 establishment_hq using "Data\Intermediate\NETS\NETS_HQs_at_least_5_employees_2014_cleaned_HQ_location_data.dta", keep(3) nogen
	rename establishment_hq hq

	reghdfe log_estab_wght_emp_density_12 log_firm_employment, a(sic8 ID_12_hq) vce(robust) 
	outreg2 using "Tables\TableI.tex", label append tex(fragment) nocons ///
		ctitle("\textit{HQ fixed effects}", "\textit{Firms with}", "\textit{$\geq 100$ plants}", "$\hspace{3.2em}\ln \bar{L}_{j}\hspace{3.2em}$") addtext(SIC8 FE, Yes, HQ Location FE, Yes, M, 12)
restore

***********************Table II: Sorting by Major Industry**********************
use "Data\Final\TablesI_II_V_and_Figure13\firm_average_location_density_M12_data.dta", clear
label variable log_firm_employment "$\ln L_{j}$"
foreach div in all manufacturing services retail FIRE {
	preserve
		if "`div'" == "all" {
			local divlabel = "\textit{All}"
			local append_replace "replace"
		}
		else if "`div'" == "retail" {
			keep if sic2>=52 & sic2<=59
			local divlabel = "\textit{Retail Trade}"
			local append_replace "append"
		}
		else if "`div'" == "services" {
			keep if sic2>=70 & sic2<=89
			local divlabel = "\textit{Services}"
			local append_replace "append"
		}
		else if "`div'" == "manufacturing" {
			keep if sic2>=20 & sic2<=39	
			local divlabel = "\textit{Manufacturing}"
			local append_replace "append"
		}
		else if "`div'" == "FIRE" {
			keep if sic2>=60 & sic2<=67
			local divlabel = "\textit{FIRE}"
			local append_replace "append"
		}
		
		reghdfe log_estab_wght_emp_density_12 log_firm_employment, a(sic8) vce(robust)
		outreg2 using "Tables\TableII.tex", label `append_replace' tex(fragment) nocons ///
			ctitle("`divlabel'", "$\hspace{3.2em}\ln \bar{L}_{j}\hspace{3.2em}$") addtext(SIC8 FE, Yes, M, 12)
	restore
}

******************Table V: Sorting Firm Size and Local Density******************
// Panel 1: Baseline
foreach m of numlist 3 6 12 24 48 {
	if `m' == 3 {
		local append_replace = "replace"
	}
	else {
		local append_replace = "append"
	}
	use "Data\Final\TablesI_II_V_and_Figure13\firm_average_location_density_M`m'_data.dta", clear
	label variable log_firm_employment "$\ln L_{j}$"
	reghdfe log_estab_wght_emp_density_`m' log_firm_employment, a(sic8) vce(robust)
	outreg2 using "Tables\TableV_panel1.tex", label `append_replace' tex(fragment) nocons nonotes ///
		ctitle("$\ln \bar{L}_{j}$") addtext(SIC8 FE, Yes, M, `m')
	
	if `m' == 12 {
		outreg2 using "Tables\TableV_panel2.tex", label replace tex(fragment) nocons nonotes ///
			ctitle("\textit{Baseline}") addtext(SIC8 FE, Yes, M, `m')
		outreg2 using "Tables\TableV_panel4.tex", label replace tex(fragment) nocons nonotes ///
			ctitle("\textit{Baseline}") addtext(SIC8 FE, Yes, M, `m')
		outreg2 using "Tables\TableV_panel6.tex", label replace tex(fragment) nocons nonotes ///
			ctitle("\textit{Baseline}", " ") addtext(SIC8 FE, Yes, M, `m')
	}
}

// Panel 2: Firms with at least X plants
// Panel 3: Controlling for HQ location, firms with at least X plants
use "Data\Final\TablesI_II_V_and_Figure13\firm_average_location_density_M12_data.dta", clear
rename hq establishment_hq
merge m:1 establishment_hq using "Data\Intermediate\NETS\NETS_HQs_at_least_5_employees_2014_cleaned_HQ_location_data.dta", keep(1 3) nogen
rename establishment_hq hq
merge m:1 hq sic8 using "Data\Intermediate\NETS\NETS_HQs_at_least_5_employees_2014_cleaned_hq-sic8_number_plants.dta", assert(3) nogen
label variable log_firm_employment "$\ln L_{j}$"

reghdfe log_estab_wght_emp_density_12 log_firm_employment if hq_sic8_num_estab >= 2, a(sic8 ID_12_hq) vce(robust) 
outreg2 using "Tables\TableV_panel3.tex", label replace tex(fragment) nocons nonotes ///
	ctitle("\textit{X = 2}") addtext(SIC8 FE, Yes, HQ Location FE, Yes, M, 12)

foreach X of numlist 10 20 50 100 {
	preserve
		merge m:1 hq sic8 using "Data\Intermediate\NETS\NETS_HQs_at_least_5_employees_2014_hq-sic8_of_firms_with_at_least_`X'_plants.dta", assert(1 3) keep(3) nogen
		reghdfe log_estab_wght_emp_density_12 log_firm_employment, a(sic8) vce(robust)
		outreg2 using "Tables\TableV_panel2.tex", label append tex(fragment) nocons nonotes ///
			ctitle("\textit{X = `X'}") addtext(SIC8 FE, Yes, M, 12)
			
		reghdfe log_estab_wght_emp_density_12 log_firm_employment if hq_sic8_num_estab >= 2, a(sic8 ID_12_hq) vce(robust) 
		outreg2 using "Tables\TableV_panel3.tex", label append tex(fragment) nocons nonotes ///
			ctitle("\textit{X = `X'}") addtext(SIC8 FE, Yes, HQ Location FE, Yes, M, 12)
	restore
}

// Panel 4: Industries where largest firm has at least X plants
// Panel 5: Controlling for HQ location, industries where largest firm has at least X plants
use "Data\Final\TablesI_II_V_and_Figure13\firm_average_location_density_M12_data.dta", clear
rename hq establishment_hq
merge m:1 establishment_hq using "Data\Intermediate\NETS\NETS_HQs_at_least_5_employees_2014_cleaned_HQ_location_data.dta", keep(1 3) nogen
rename establishment_hq hq
merge m:1 hq sic8 using "Data\Intermediate\NETS\NETS_HQs_at_least_5_employees_2014_cleaned_hq-sic8_number_plants.dta", assert(3) nogen
label variable log_firm_employment "$\ln L_{j}$"

reghdfe log_estab_wght_emp_density_12 log_firm_employment if hq_sic8_num_estab >= 2, a(sic8 ID_12_hq) vce(robust) 
outreg2 using "Tables\TableV_panel5.tex", label replace tex(fragment) nocons nonotes ///
	ctitle("\textit{Baseline}") addtext(SIC8 FE, Yes, HQ Location FE, Yes, M, 12)

foreach X of numlist 10 20 50 100 {
	preserve
		merge m:1 hq sic8 using "Data\Intermediate\NETS\NETS_HQs_at_least_5_employees_2014_hq-sic8_of_firms_in_industries_where_largest_firm_has_at_least_`X'_plants.dta", assert(1 3) keep(3) nogen
		reghdfe log_estab_wght_emp_density_12 log_firm_employment, a(sic8) vce(robust)
		outreg2 using "Tables\TableV_panel4.tex", label append tex(fragment) nocons nonotes ///
			ctitle("\textit{X = `X'}") addtext(SIC8 FE, Yes, M, 12)
			
		reghdfe log_estab_wght_emp_density_12 log_firm_employment if hq_sic8_num_estab >= 2, a(sic8 ID_12_hq) vce(robust) 
		outreg2 using "Tables\TableV_panel5.tex", label append tex(fragment) nocons nonotes ///
			ctitle("\textit{X = `X'}") addtext(SIC8 FE, Yes, HQ Location FE, Yes, M, 12)
	restore
}

// Panel 6
// excluding own contribution total firm employment
use "Data\Final\TablesI_II_V_and_Figure13\firm_average_location_density_M12_excluding_own_contribution_data.dta", clear
label variable log_firm_employment "$\ln L_{j}$"
reghdfe log_estab_wght_emp_density_12 log_firm_employment, a(sic8) vce(robust)
outreg2 using "Tables\TableV_panel6.tex", label append tex(fragment) nocons nonotes ///
	ctitle("\textit{Excluding Own}", "\textit{Contribution}") addtext(SIC8 FE, Yes, M, 12)

// average employment density, equally weighted
use "Data\Final\TablesI_II_V_and_Figure13\firm_average_location_density_M12_data.dta", clear
label variable log_firm_employment "$\ln L_{j}$"
reghdfe log_unwght_avg_emp_density_12 log_firm_employment, a(sic8) vce(robust)
outreg2 using "Tables\TableV_panel6.tex", label append tex(fragment) nocons nonotes ///
	ctitle("\textit{Unweighted}", " ") addtext(SIC8 FE, Yes, M, 12)

// average employment density, weighted by employment
reghdfe log_emp_wght_emp_density_12 log_firm_employment, a(sic8) vce(robust)
outreg2 using "Tables\TableV_panel6.tex", label append tex(fragment) nocons nonotes ///
	ctitle("\textit{Weighted by}", "\textit{Employment}") addtext(SIC8 FE, Yes, M, 12)

// using non-imputed data
use "Data\Final\TablesI_II_V_and_Figure13\firm_average_location_density_M12_nonimputed_data.dta", clear
label variable log_firm_employment "$\ln L_{j}$"
reghdfe log_estab_wght_emp_density_12 log_firm_employment, a(sic8) vce(robust)
outreg2 using "Tables\TableV_panel6.tex", label append tex(fragment) nocons nonotes ///
	ctitle("\textit{Non-imputed}", " ") addtext(SIC8 FE, Yes, M, 12)
